# -*- coding: utf-8 -*-
"""
通知服务 - 负责发送报表通知（企业微信、邮件等）
"""
import json
import logging
import pymysql.cursors
import requests
from typing import Dict, List, Optional, Any
from datetime import datetime

from app.services.report_service import ReportRunService
from app.utils.mysql_db import get_db_connection

logger = logging.getLogger(__name__)


class NotificationService:
    """通知服务"""
    
    @staticmethod
    def send_wecom_notification(webhook_url: str, content: str, 
                               mention: Dict = None) -> Dict[str, Any]:
        """
        发送企业微信通知
        :param webhook_url: 企业微信机器人Webhook地址
        :param content: 通知内容
        :param mention: @配置 {"to_user":[],"to_party":[],"to_tag":[],"is_all":false}
        :return: 发送结果
        """
        try:
            # 处理@成员配置
            # mention 期望格式: {"to_user":[], "to_party":[], "to_tag":[], "is_all": false}
            to_user = []
            is_all = False
            if mention:
                try:
                    to_user = mention.get('to_user') if isinstance(mention.get('to_user'), list) else []
                except Exception:
                    to_user = []
                
                # 兼容多种布尔字段名称
                is_all = bool(mention.get('is_all') or mention.get('isAtAll') or mention.get('is_all') == True)
            
            # 根据企业微信官方文档，markdown 消息中@成员需要在内容中使用 <@userid> 格式
            # 注意：markdown 消息不支持根级别的 mentioned_list，只能在 content 中使用 <@userid> 格式
            processed_content = content
            if to_user and not is_all:
                # 在内容末尾添加@成员标记（企业微信要求）
                # 格式：<@userid1> <@userid2>
                at_tags = ' '.join([f'<@{userid}>' for userid in to_user if userid])
                if at_tags:
                    # 如果内容末尾没有换行，先添加换行
                    if not processed_content.endswith('\n'):
                        processed_content += '\n'
                    processed_content += at_tags
                    logger.info(f"添加@成员标记: {at_tags}, to_user={to_user}")
            elif is_all:
                # @所有人：在内容中使用 <@all>
                if not processed_content.endswith('\n'):
                    processed_content += '\n'
                processed_content += '<@all>'
                logger.info("添加@所有人标记")
            
            # 构建消息体（使用 markdown 格式）
            msg = {
                "msgtype": "markdown",
                "markdown": {
                    "content": processed_content
                }
            }
            
            # 记录发送的消息内容（用于调试）
            logger.debug(f"发送企业微信消息: msgtype=markdown, content长度={len(processed_content)}, 包含@标记={bool(to_user or is_all)}")
            
            # 发送请求
            response = requests.post(
                webhook_url,
                json=msg,
                headers={'Content-Type': 'application/json'},
                timeout=10
            )
            
            result = response.json()
            
            return {
                'success': result.get('errcode') == 0,
                'status_code': response.status_code,
                'response': result,
                'error': result.get('errmsg') if result.get('errcode') != 0 else None
            }
        except Exception as e:
            return {
                'success': False,
                'status_code': 0,
                'response': None,
                'error': str(e)
            }
    
    @staticmethod
    def send_email_notification(recipients: List[str], subject: str, 
                               body: str, cc: List[str] = None) -> Dict[str, Any]:
        """
        发送邮件通知（占位，待实现）
        :param recipients: 收件人列表
        :param subject: 邮件主题
        :param body: 邮件正文
        :param cc: 抄送列表
        :return: 发送结果
        """
        # TODO: 实现邮件发送功能
        return {
            'success': False,
            'status_code': 0,
            'response': None,
            'error': '邮件功能尚未实现'
        }
    
    @staticmethod
    def log_delivery(run_id: int, webhook_id: int, status: str = 'success',
                    request_body: Dict = None, response_code: int = None,
                    response_body: Dict = None, error_message: str = None):
        """
        记录通知发送日志
        :param run_id: 运行ID
        :param webhook_id: webhook库ID
        :param status: 状态（success/failed）
        :param request_body: 请求体
        :param response_code: 响应状态码
        :param response_body: 响应体
        :param error_message: 错误信息
        """
        # 验证 webhook_id 是否有效
        if not webhook_id:
            logger.warning(f"跳过记录日志：webhook_id 为空，run_id={run_id}")
            return
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            # 先验证 webhook_id 是否存在
            cursor.execute("SELECT id FROM report_webhook_library WHERE id = %s", (webhook_id,))
            if not cursor.fetchone():
                logger.warning(f"跳过记录日志：webhook_id={webhook_id} 不存在于 report_webhook_library 表中，run_id={run_id}")
                return
            
            sql = """
                INSERT INTO report_delivery_logs
                (run_id, webhook_id, status, request_body, response_code, response_body, error_message)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (
                run_id, webhook_id, status,
                json.dumps(request_body, ensure_ascii=False) if request_body else None,
                response_code,
                json.dumps(response_body, ensure_ascii=False) if response_body else None,
                error_message
            ))
            conn.commit()
        except Exception as e:
            logger.error(f"记录通知发送日志失败：{str(e)}", exc_info=True)
            conn.rollback()
        finally:
            cursor.close()
            conn.close()
    
    @staticmethod
    def send_report_notification(run_id: int, notify_params: Dict = None):
        """
        发送报表生成通知
        :param run_id: 运行ID
        :param notify_params: 通知参数
            - 如果包含 webhook_id: 使用webhook库中的记录
            - 如果包含 webhook_url 和 channel_type: 使用临时webhook配置
            - 如果包含 webhook_ids (列表): 使用多个webhook
            - 如果包含 webhooks (列表): 使用多个临时webhook配置
            - 否则: 从配置中获取
        """
        conn = get_db_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        try:
            # 获取运行信息
            sql_run = """
                SELECT r.*, t.template_name
                FROM report_runs r
                LEFT JOIN report_templates t ON r.template_code = t.template_code
                WHERE r.id = %s
            """
            cursor.execute(sql_run, (run_id,))
            run = cursor.fetchone()
            
            if not run:
                return
            
            # 格式化时间字段（与 ReportRunService.get_run_by_id 保持一致）
            if run.get('created_at') and hasattr(run['created_at'], 'strftime'):
                run['created_at'] = run['created_at'].strftime('%Y-%m-%d %H:%M:%S')
            if run.get('finished_at') and hasattr(run['finished_at'], 'strftime'):
                run['finished_at'] = run['finished_at'].strftime('%Y-%m-%d %H:%M:%S')
            # time_start 和 time_end 可能为 None（表示全部数据），只有在非 None 时才格式化
            if run.get('time_start') and hasattr(run['time_start'], 'strftime'):
                run['time_start'] = run['time_start'].strftime('%Y-%m-%d %H:%M:%S')
            if run.get('time_end') and hasattr(run['time_end'], 'strftime'):
                run['time_end'] = run['time_end'].strftime('%Y-%m-%d %H:%M:%S')
            
            webhooks_to_send = []
            
            # 确定通知参数
            if notify_params:
                # 情况1: 使用webhook_id (单个)
                if notify_params.get('webhook_id'):
                    webhook_id = notify_params.get('webhook_id')
                    cursor.execute("""
                        SELECT * FROM report_webhook_library
                        WHERE id = %s AND is_active = 1
                    """, (webhook_id,))
                    webhook = cursor.fetchone()
                    if webhook:
                        webhooks_to_send.append(webhook)
                
                # 情况2: 使用webhook_ids (多个)
                elif notify_params.get('webhook_ids'):
                    webhook_ids = notify_params.get('webhook_ids')
                    if isinstance(webhook_ids, list) and webhook_ids:
                        placeholders = ','.join(['%s'] * len(webhook_ids))
                        cursor.execute(f"""
                            SELECT * FROM report_webhook_library
                            WHERE id IN ({placeholders}) AND is_active = 1
                        """, webhook_ids)
                        webhooks_to_send.extend(cursor.fetchall())
                
                # 情况3: 使用临时webhook配置 (单个)
                elif notify_params.get('webhook_url') and notify_params.get('channel_type'):
                    # 构建临时webhook配置
                    temp_webhook = {
                        'id': None,  # 临时配置，无ID
                        'name': '临时通知',
                        'channel_type': notify_params.get('channel_type'),
                        'webhook_url': notify_params.get('webhook_url'),
                        'wecom_mention': notify_params.get('wecom_mention'),
                        'email_recipients': notify_params.get('email_recipients'),
                        'email_cc': notify_params.get('email_cc')
                    }
                    webhooks_to_send.append(temp_webhook)
                
                # 情况4: 使用临时webhooks配置 (多个)
                elif notify_params.get('webhooks'):
                    webhooks = notify_params.get('webhooks')
                    if isinstance(webhooks, list):
                        for webhook_config in webhooks:
                            channel_type = webhook_config.get('channel_type')
                            # 对于email渠道，检查email_recipients；对于其他渠道，检查webhook_url
                            if channel_type == 'email':
                                if webhook_config.get('email_recipients'):
                                    temp_webhook = {
                                        'id': None,
                                        'name': '临时通知',
                                        'channel_type': 'email',
                                        'email_recipients': webhook_config.get('email_recipients'),
                                        'email_cc': webhook_config.get('email_cc')
                                    }
                                    webhooks_to_send.append(temp_webhook)
                            elif channel_type and webhook_config.get('webhook_url'):
                                temp_webhook = {
                                    'id': None,
                                    'name': '临时通知',
                                    'channel_type': channel_type,
                                    'webhook_url': webhook_config.get('webhook_url'),
                                    'wecom_mention': webhook_config.get('wecom_mention')
                                }
                                webhooks_to_send.append(temp_webhook)
                
                # 情况5: 使用channels和对应的webhook配置 (向后兼容)
                elif notify_params.get('channels'):
                    channels = notify_params.get('channels')
                    if isinstance(channels, list):
                        for channel in channels:
                            if channel == 'wecom' and notify_params.get('wecom_webhook_url'):
                                temp_webhook = {
                                    'id': None,
                                    'name': '临时通知',
                                    'channel_type': 'wecom',
                                    'webhook_url': notify_params.get('wecom_webhook_url'),
                                    'wecom_mention': notify_params.get('wecom_mention')
                                }
                                webhooks_to_send.append(temp_webhook)
                            elif channel == 'email' and notify_params.get('email_recipients'):
                                temp_webhook = {
                                    'id': None,
                                    'name': '临时通知',
                                    'channel_type': 'email',
                                    'email_recipients': notify_params.get('email_recipients'),
                                    'email_cc': notify_params.get('email_cc')
                                }
                                webhooks_to_send.append(temp_webhook)
            
            # 如果没有通过notify_params指定，从配置中获取
            if not webhooks_to_send:
                if not run['report_config_id']:
                    return  # 无配置，不发送通知
                
                sql_notif = """
                    SELECT 
                        n.id as notification_id,
                        n.report_config_id,
                        n.enabled,
                        w.id as webhook_id,
                        w.name,
                        w.channel_type,
                        w.webhook_url,
                        w.wecom_mention,
                        w.email_recipients,
                        w.email_cc,
                        w.is_active
                    FROM report_config_notifications n
                    INNER JOIN report_webhook_library w ON n.webhook_id = w.id
                    WHERE n.report_config_id = %s 
                      AND n.enabled = 1 
                      AND w.is_active = 1
                      AND w.id IS NOT NULL
                """
                cursor.execute(sql_notif, (run['report_config_id'],))
                webhooks_to_send = cursor.fetchall()
                
                # 验证查询结果
                if webhooks_to_send:
                    logger.info(f"找到 {len(webhooks_to_send)} 个通知配置，run_id={run_id}")
                    for webhook in webhooks_to_send:
                        if not webhook.get('webhook_id'):
                            logger.warning(f"警告：通知配置的 webhook_id 为空，notification_id={webhook.get('notification_id')}")
            
            resolved_webhooks = []
            channels_set = set()

            # 发送通知
            for webhook in webhooks_to_send:
                channel_type = webhook.get('channel_type')
                if channel_type:
                    channels_set.add(channel_type)
                metadata = NotificationService._send_notification_for_run(run, webhook)
                if metadata:
                    resolved_webhooks.append(metadata)

            # 更新运行记录的通知元数据
            if webhooks_to_send:
                final_channels = list(channels_set)
                final_notify_params = {
                    'source': 'custom' if notify_params else 'config',
                    'send_notification': True,
                    'channels': final_channels,
                    'webhooks': resolved_webhooks,
                    'attempted_count': len(webhooks_to_send)
                }

                if notify_params is not None:
                    original_params = notify_params
                    if isinstance(original_params, str):
                        try:
                            original_params = json.loads(original_params)
                        except Exception:
                            pass
                    final_notify_params['original_params'] = original_params

                ReportRunService.update_run_notification(
                    run_id,
                    notify_requested=True,
                    notify_channels=final_channels,
                    notify_params=final_notify_params
                )
        finally:
            cursor.close()
            conn.close()
    
    @staticmethod
    def _send_notification_for_run(run: Dict, webhook: Dict):
        """
        为运行发送通知（根据webhook类型自动选择渠道）
        :param run: 运行记录
        :param webhook: webhook库记录或临时webhook配置（id可能为None）
        """
        # 支持两种格式：'id' 或 'webhook_id'（查询结果中可能使用别名）
        webhook_id = webhook.get('webhook_id') or webhook.get('id')  # 临时配置时可能为None
        channel_type = webhook['channel_type']
        metadata = {
            'channel_type': channel_type,
            'webhook_id': webhook_id,
            'name': webhook.get('name') or webhook.get('webhook_name')
        }
        
        # 仅发送成功通知
        if run['status'] != 'success':
            logger.info(f"跳过发送通知：run_id={run['id']} 状态为 {run['status']}")
            return
        
        # 构建通知内容
        status_icon = "✅"
        status_text = "成功"
        
        # 报表名称：优先使用 title，如果为空则使用模板名称
        report_name = run.get('title') or run.get('template_name') or '未知报表'
        
        # 统计时间：如果 time_start 和 time_end 都为 None，显示"全部数据"
        if run.get('time_start') and run.get('time_end'):
            time_range = f"{run['time_start']} 至 {run['time_end']}"
        else:
            time_range = "全部数据"
        
        content = (
            f"{status_icon} <font color=\"info\">报表生成{status_text}</font>\n"
            f"> 📄 报表名称：**{report_name}**\n"
            f"> 🔁 运行ID：`{run['id']}`\n"
            f"> 📊 统计时间：{time_range}\n"
            f"> 🕒 生成时间：{run['created_at']}\n\n"
        )
        
        # 生成报表分享页面链接
        import logging
        import os
        import socket
        from flask import current_app
        logger = logging.getLogger('flink.business')
        
        # 获取前端基础URL（分享页面通过前端路由访问）
        # 优先从环境变量获取前端URL
        frontend_url = os.getenv('FRONTEND_URL') or os.getenv('BASE_URL') or os.getenv('APP_URL')
        
        # 如果没有配置前端URL，尝试获取后端URL并转换为前端URL
        if not frontend_url:
            backend_url = os.getenv('BACKEND_URL') or os.getenv('API_BASE_URL')
            if backend_url:
                # 如果配置了后端URL，尝试推断前端URL（假设前端端口是5173）
                try:
                    from urllib.parse import urlparse
                    parsed = urlparse(backend_url)
                    # 将端口改为前端端口
                    frontend_port = os.getenv('FRONTEND_PORT') or '5173'
                    frontend_url = f"{parsed.scheme}://{parsed.hostname}:{frontend_port}"
                except:
                    pass
        
        if not frontend_url:
            # 尝试从Flask配置获取（需要在应用上下文中）
            try:
                from flask import has_app_context
                if has_app_context():
                    frontend_url = current_app.config.get('FRONTEND_URL') or current_app.config.get('BASE_URL')
            except:
                pass
        
        if not frontend_url:
            # 尝试获取本机网络IP地址（避免使用localhost）
            import logging
            logger = logging.getLogger('flink.business')
            
            try:
                # 获取本机主机名
                hostname = socket.gethostname()
                # 获取本机IP地址（优先获取非127.0.0.1的地址）
                host_ip = socket.gethostbyname(hostname)
                
                # 如果获取到的是127.0.0.1，尝试获取实际网络IP
                if host_ip == '127.0.0.1' or host_ip == '::1':
                    # 尝试连接到外部地址来获取本机IP
                    try:
                        s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
                        # 连接到一个外部地址（不会真正连接）
                        s.connect(('8.8.8.8', 80))
                        host_ip = s.getsockname()[0]
                        s.close()
                    except:
                        # 如果获取失败，使用hostname
                        pass
                
                # 获取前端端口（从环境变量或使用默认值5173）
                frontend_port = os.getenv('FRONTEND_PORT') or '5173'
                
                frontend_url = f"http://{host_ip}:{frontend_port}"
                logger.info(f"自动获取前端URL: {frontend_url} (hostname: {hostname}, ip: {host_ip}, port: {frontend_port})")
            except Exception as e:
                logger.warning(f"获取网络IP失败，使用localhost: {str(e)}")
                # 如果获取网络IP失败，使用localhost（开发环境）
                frontend_port = os.getenv('FRONTEND_PORT') or '5173'
                frontend_url = f'http://localhost:{frontend_port}'
        
        # 构建完整的分享URL（通过前端路由访问）
        share_url = f"{frontend_url.rstrip('/')}/#/reports/share/{run['id']}"
        
        logger.info(
            f"生成报表分享链接 | run_id: {run['id']}, "
            f"frontend_url: {frontend_url}, share_url: {share_url}"
        )
        
        # 企业微信markdown链接格式：[链接文本](链接地址)
        # 使用友好的链接文本，确保链接可点击
        content += (
            f"🔗 **查看报表**：[点击查看报表]({share_url})\n"
            f"> 💡 分享页面支持预览 HTML 报表并下载全部文件\n"
            f"> 🔗 链接地址：{share_url}\n"
        )
        
        # 根据渠道类型发送通知
        result = None
        
        if channel_type in ('wecom', 'dingtalk', 'feishu'):
            # 企业微信/钉钉/飞书等使用webhook发送
            webhook_url = webhook['webhook_url']
            mention = None
            metadata['webhook_url'] = webhook_url
            
            if channel_type == 'wecom' and webhook.get('wecom_mention'):
                mention_raw = webhook['wecom_mention']
                mention = json.loads(mention_raw) if isinstance(mention_raw, str) else mention_raw
                logger.info(f"从webhook获取wecom_mention: {mention}, 类型={type(mention)}")
            else:
                logger.info(f"未找到wecom_mention配置: channel_type={channel_type}, has_wecom_mention={bool(webhook.get('wecom_mention'))}")
            metadata['wecom_mention'] = mention
            
            result = NotificationService.send_wecom_notification(
                webhook_url, content, mention
            )
            
            # 记录日志（只有webhook_id存在时才记录到数据库）
            if webhook_id:
                NotificationService.log_delivery(
                    run['id'], webhook_id,
                    status='success' if result['success'] else 'failed',
                    request_body={'content': content, 'mention': mention},
                    response_code=result['status_code'],
                    response_body=result['response'],
                    error_message=result.get('error')
                )
        
        elif channel_type == 'email':
            # 邮件通知
            # 处理email_recipients（可能是字符串、列表或None）
            recipients = webhook.get('email_recipients', [])
            if isinstance(recipients, str):
                try:
                    recipients = json.loads(recipients)
                except (json.JSONDecodeError, TypeError):
                    # 如果不是JSON，可能是逗号分隔的字符串
                    recipients = [r.strip() for r in recipients.split(',') if r.strip()]
            elif not isinstance(recipients, list):
                recipients = []
            
            # 处理email_cc（可能是字符串、列表或None）
            cc = webhook.get('email_cc', [])
            if isinstance(cc, str):
                try:
                    cc = json.loads(cc)
                except (json.JSONDecodeError, TypeError):
                    cc = [c.strip() for c in cc.split(',') if c.strip()]
            elif not isinstance(cc, list):
                cc = []
            
            subject = webhook.get('email_subject_template', f'报表生成{status_text}')
            metadata['email_recipients'] = recipients
            metadata['email_cc'] = cc
            metadata['email_subject'] = subject
            
            result = NotificationService.send_email_notification(
                recipients, subject, content, cc
            )
            
            # 记录日志（只有webhook_id存在时才记录到数据库）
            if webhook_id:
                NotificationService.log_delivery(
                    run['id'], webhook_id,
                    status='success' if result['success'] else 'failed',
                    request_body={'subject': subject, 'body': content, 'recipients': recipients},
                    response_code=result['status_code'],
                    response_body=result['response'],
                    error_message=result.get('error')
                )
        else:
            # 其他webhook类型（兜底）
            metadata['webhook_url'] = webhook.get('webhook_url')
            result = None

        metadata['send_result'] = result
        return metadata


class ReportNotificationConfigService:
    """报表通知配置服务"""
    
    @staticmethod
    def get_notifications_by_config(config_id: int) -> List[Dict]:
        """
        获取配置的通知列表（关联webhook库信息）
        :param config_id: 配置ID
        :return: 通知配置列表
        """
        conn = get_db_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        try:
            sql = """
                SELECT n.*, w.id as webhook_id, w.name as webhook_name, w.channel_type, w.webhook_url, 
                       w.wecom_mention, w.email_recipients, w.email_cc, w.description, w.is_active
                FROM report_config_notifications n
                JOIN report_webhook_library w ON n.webhook_id = w.id
                WHERE n.report_config_id = %s
                ORDER BY n.id
            """
            cursor.execute(sql, (config_id,))
            return cursor.fetchall()
        finally:
            cursor.close()
            conn.close()
    
    @staticmethod
    def create_notification(config_id: int, webhook_id: int = None, enabled: bool = True,
                           channel: str = None, wecom_webhook_url: str = None,
                           wecom_mention: Dict = None, email_recipients: List = None,
                           email_cc: List = None) -> int:
        """
        创建通知配置
        :param config_id: 配置ID
        :param webhook_id: webhook库ID（如果提供，直接使用，不创建新的 webhook 库记录）
        :param enabled: 是否启用
        :param channel: 渠道类型（wecom/email等），仅在 webhook_id 为空时使用
        :param wecom_webhook_url: 企业微信Webhook地址，仅在 webhook_id 为空时使用
        :param wecom_mention: 企业微信@配置，仅在 webhook_id 为空时使用
        :param email_recipients: 邮件收件人列表，仅在 webhook_id 为空时使用
        :param email_cc: 邮件抄送列表，仅在 webhook_id 为空时使用
        :return: 通知ID
        """
        import json
        
        conn = get_db_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        try:
            # 如果提供了 webhook_id，直接使用（不创建新的 webhook 库记录）
            if webhook_id:
                # 验证 webhook_id 是否存在
                cursor.execute("""
                    SELECT id, channel_type FROM report_webhook_library 
                    WHERE id = %s AND is_active = 1
                """, (webhook_id,))
                webhook = cursor.fetchone()
                if not webhook:
                    raise ValueError(f"webhook_id={webhook_id} 不存在或已禁用")
                
                # 如果同时提供了 wecom_mention，更新 webhook 库中的 wecom_mention
                # 这样可以为同一个 webhook 在不同配置中使用不同的 @ 成员
                if wecom_mention and webhook.get('channel_type') == 'wecom':
                    wecom_mention_json = json.dumps(wecom_mention, ensure_ascii=False) if wecom_mention else None
                    cursor.execute("""
                        UPDATE report_webhook_library
                        SET wecom_mention = %s
                        WHERE id = %s
                    """, (wecom_mention_json, webhook_id))
                    conn.commit()
                    logger.info(f"更新 webhook_id={webhook_id} 的 wecom_mention: {wecom_mention}")
            # 如果没有提供 webhook_id，需要先查找或创建 webhook 库记录
            elif channel:
                # 根据 channel 类型查找或创建 webhook 库记录
                if channel == 'wecom':
                    if not wecom_webhook_url:
                        raise ValueError("wecom 渠道必须提供 wecom_webhook_url")
                    
                    # 检查是否已存在相同的 webhook（根据 webhook_url 和 channel_type）
                    cursor.execute("""
                        SELECT id FROM report_webhook_library 
                        WHERE channel_type = 'wecom' AND webhook_url = %s
                    """, (wecom_webhook_url,))
                    existing = cursor.fetchone()
                    
                    if existing:
                        webhook_id = existing['id']
                    else:
                        # 创建新的 webhook 库记录（用户输入了新 URL）
                        webhook_name = f"配置_{config_id}_wecom"
                        wecom_mention_json = json.dumps(wecom_mention, ensure_ascii=False) if wecom_mention else None
                        cursor.execute("""
                            INSERT INTO report_webhook_library
                            (name, channel_type, webhook_url, wecom_mention)
                            VALUES (%s, %s, %s, %s)
                        """, (webhook_name, 'wecom', wecom_webhook_url, wecom_mention_json))
                        webhook_id = cursor.lastrowid
                        conn.commit()
                        
                elif channel == 'email':
                    if not email_recipients:
                        raise ValueError("email 渠道必须提供 email_recipients")
                    
                    # 检查是否已存在相同的 webhook（根据 email_recipients 和 channel_type）
                    email_recipients_json = json.dumps(email_recipients, ensure_ascii=False)
                    cursor.execute("""
                        SELECT id FROM report_webhook_library 
                        WHERE channel_type = 'email' AND email_recipients = %s
                    """, (email_recipients_json,))
                    existing = cursor.fetchone()
                    
                    if existing:
                        webhook_id = existing['id']
                    else:
                        # 创建新的 webhook 库记录（用户输入了新邮箱）
                        webhook_name = f"配置_{config_id}_email"
                        email_cc_json = json.dumps(email_cc, ensure_ascii=False) if email_cc else None
                        cursor.execute("""
                            INSERT INTO report_webhook_library
                            (name, channel_type, email_recipients, email_cc)
                            VALUES (%s, %s, %s, %s)
                        """, (webhook_name, 'email', email_recipients_json, email_cc_json))
                        webhook_id = cursor.lastrowid
                        conn.commit()
                else:
                    raise ValueError(f"不支持的渠道类型: {channel}")
            else:
                raise ValueError("必须提供 webhook_id 或 channel")
            
            # 检查通知配置是否已存在（由于 UNIQUE KEY）
            cursor.execute("""
                SELECT id FROM report_config_notifications
                WHERE report_config_id = %s AND webhook_id = %s
            """, (config_id, webhook_id))
            existing_notif = cursor.fetchone()
            
            if existing_notif:
                # 如果已存在，更新 enabled 状态
                cursor.execute("""
                    UPDATE report_config_notifications
                    SET enabled = %s
                    WHERE id = %s
                """, (1 if enabled else 0, existing_notif['id']))
                conn.commit()
                return existing_notif['id']
            else:
                # 创建新的通知配置
                sql = """
                    INSERT INTO report_config_notifications
                    (report_config_id, webhook_id, enabled)
                    VALUES (%s, %s, %s)
                """
                cursor.execute(sql, (config_id, webhook_id, 1 if enabled else 0))
                conn.commit()
                return cursor.lastrowid
        except Exception as e:
            # 回滚事务
            try:
                conn.rollback()
            except:
                pass
            logger.error(f"创建通知配置失败: {str(e)}", exc_info=True)
            raise
        finally:
            cursor.close()
            conn.close()
    
    @staticmethod
    def update_notification(notification_id: int, **kwargs) -> bool:
        """
        更新通知配置
        :param notification_id: 通知ID
        :param kwargs: 要更新的字段（webhook_id, enabled）
        :return: 是否成功
        """
        if not kwargs:
            return False
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            updates = []
            values = []
            
            if 'webhook_id' in kwargs:
                updates.append("webhook_id = %s")
                values.append(kwargs['webhook_id'])
            
            if 'enabled' in kwargs:
                updates.append("enabled = %s")
                values.append(1 if kwargs['enabled'] else 0)
            
            if not updates:
                return False
            
            values.append(notification_id)
            
            sql = f"UPDATE report_config_notifications SET {', '.join(updates)} WHERE id = %s"
            cursor.execute(sql, values)
            conn.commit()
            return cursor.rowcount > 0
        finally:
            cursor.close()
            conn.close()
    
    @staticmethod
    def delete_notification(notification_id: int) -> bool:
        """
        删除通知配置
        :param notification_id: 通知ID
        :return: 是否成功
        """
        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            sql = "DELETE FROM report_config_notifications WHERE id = %s"
            cursor.execute(sql, (notification_id,))
            conn.commit()
            return cursor.rowcount > 0
        finally:
            cursor.close()
            conn.close()
    
    @staticmethod
    def delete_notifications_by_config(config_id: int) -> bool:
        """
        删除配置下的所有通知配置
        :param config_id: 配置ID
        :return: 是否成功
        """
        conn = get_db_connection()
        cursor = conn.cursor()
        
        try:
            sql = "DELETE FROM report_config_notifications WHERE report_config_id = %s"
            cursor.execute(sql, (config_id,))
            conn.commit()
            return True
        finally:
            cursor.close()
            conn.close()

